package com.gdk.ksoft.assembly.dbhelper;

import android.content.Context;
import android.database.Cursor;
import android.text.TextUtils;

import com.gdk.ksoft.data.entry.tables.GoodsModuleTable;
import com.gdk.ksoft.data.entry.tables.GoodsTable;
import com.gdk.ksoft.data.entry.tables.SaleTable;
import com.gdk.ksoft.data.entry.tables.ShoppingCartGoodsTable;
import com.gdk.ksoft.data.entry.tables.UnsoldGoodsTable;
import com.gdk.ksoft.ui.works.models.FworkCustomerModel;
import com.gdk.ksoft.ui.works.models.FworkGoodsOrderModel;
import com.gdk.ksoft.assembly.utils.extensions.FormatUtils;
import com.gdk.ksoft.assembly.utils.system.SQLiteUtils;
import com.lidroid.xutils.DbUtils;
import com.lidroid.xutils.db.sqlite.Selector;
import com.lidroid.xutils.db.sqlite.SqlInfo;
import com.lidroid.xutils.db.sqlite.WhereBuilder;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Locale;

/**
 * Created by Mrper on 2014/11/25.
 * 主页数据操作 --- 工作页
 */
public class FworkDbHelper {

    /**
     * 查询所有客户数据
     * @param keyword
     * @throws Exception
     */
    public static List<FworkCustomerModel> queryAllCustomers(DbUtils dbUtils,String keyword) throws Exception {
        String sql = "SELECT kehu.id,kehu.mc,kehu_lx.dttb,kehu.lxr,kehu.dh,kehu_shdz.shr,kehu_shdz.shdh,kehu_shdz.shdz,kehu_shdz.jwd,kehu_shdz.did,kehu_shdz.id,kehu.jg_mc "
                +"FROM kehu,kehu_shdz,kehu_lx "
                +"WHERE kehu.id <> 0 AND kehu_shdz.id <> 0 AND kehu.id = kehu_shdz.kh_id AND kehu_shdz.khlx = kehu_lx.id AND "
                +"(kehu.mc LIKE '%"+keyword+"%' OR kehu.kjj LIKE '%"+keyword+"%' OR kehu_shdz.shdz LIKE '%"+keyword+"%')";
        System.out.println("------> 查询客户的SQL语句是： "+sql);
        List<FworkCustomerModel> customerlst = new ArrayList<FworkCustomerModel>();
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            FworkCustomerModel model = new FworkCustomerModel();
            model.netCustomerId = cursor.getInt(0/*cursor.getColumnIndex("kehu.id")*/);//获取客户网络ID
            model.customerName = cursor.getString(1/*cursor.getColumnIndex("kehu.mc")*/);//获取客户名称
            model.customerType = cursor.getString(2/*cursor.getColumnIndex("kehu_lx.dttb")*/);//获取客户地图图标
            model.contactorName = cursor.getString(3/*cursor.getColumnIndex("kehu.lxr")*/);//获取联系人姓名
            model.contactorPhone = cursor.getString(4/*cursor.getColumnIndex("kehu.dh")*/);//获取联系人电话
            model.receiverName = cursor.getString(5/*cursor.getColumnIndex("kehu_shdz.shr")*/);//获取收货人姓名
            model.receiverPhone = cursor.getString(6/*cursor.getColumnIndex("kehu_shdz.shdh")*/);//获取收货人电话
            model.receiverAddress = cursor.getString(7/*cursor.getColumnIndex("kehu_shdz.shdz")*/);//获取送货地址
            model.latLng = cursor.getString(8/*cursor.getColumnIndex("kehu_shdz.jwd")*/);//获取送货地址经纬度
            model.localeAddressId = cursor.getInt(9/*cursor.getColumnIndex("kehu_shdz.did")*/);//获取送货地址本地ID
            model.netAddressId = cursor.getInt(10/*cursor.getColumnIndex("kehu_shdz.id")*/);//获取送货地址网络ID
            model.priceName = cursor.getString(11/*cursor.getColumnIndex("kehu.jg_mc")*/);//获取客户价格名称
            customerlst.add(model);
            System.out.println("-----> 客户经纬度值："+model.latLng);
        }
        cursor.close();
        return customerlst;
    }

    /**
     * 查询附近的客户
     * @return
     */
    public static List<FworkCustomerModel> findNeighborhoodCustomers(DbUtils dbUtils)throws Exception{
        List<FworkCustomerModel> lst = new ArrayList<>();
        Cursor cursor = dbUtils.execQuery("SELECT kehu.id,kehu.mc,kehu_lx.dttb,kehu.lxr,kehu.dh,kehu_shdz.shr,kehu_shdz.shdh,kehu_shdz.shdz,kehu_shdz.jwd,kehu_shdz.did,kehu_shdz.id,kehu.jg_mc "
                        +"FROM kehu,kehu_shdz,kehu_lx "
                        +"WHERE  kehu.id <> 0 AND  kehu_shdz.id <> 0 AND kehu.id = kehu_shdz.kh_id AND kehu_shdz.khlx = kehu_lx.id AND jwd<>'';");
        while(cursor.moveToNext()){
            FworkCustomerModel ct = new FworkCustomerModel();
            ct.netCustomerId = cursor.getInt(0/*cursor.getColumnIndex("kehu.id")*/);//获取客户网络ID
            ct.customerName = cursor.getString(1/*cursor.getColumnIndex("kehu.mc")*/);//获取客户名称
            ct.customerType = cursor.getString(2/*cursor.getColumnIndex("kehu_lx.dttb")*/);//获取客户地图图标
            ct.contactorName = cursor.getString(3/*cursor.getColumnIndex("kehu.lxr")*/);//获取联系人姓名
            ct.contactorPhone = cursor.getString(4/*cursor.getColumnIndex("kehu.dh")*/);//获取联系人电话
            ct.receiverName = cursor.getString(5/*cursor.getColumnIndex("kehu_shdz.shr")*/);//获取收货人姓名
            ct.receiverPhone = cursor.getString(6/*cursor.getColumnIndex("kehu_shdz.shdh")*/);//获取收货人电话
            ct.receiverAddress = cursor.getString(7/*cursor.getColumnIndex("kehu_shdz.shdz")*/);//获取送货地址
            ct.latLng = cursor.getString(8/*cursor.getColumnIndex("kehu_shdz.jwd")*/);//获取送货地址经纬度
            ct.localeAddressId = cursor.getInt(9/*cursor.getColumnIndex("kehu_shdz.did")*/);//获取送货地址本地ID
            ct.netAddressId = cursor.getInt(10/*cursor.getColumnIndex("kehu_shdz.id")*/);//获取送货地址网络ID
            ct.priceName = cursor.getString(11/*cursor.getColumnIndex("kehu.jg_mc")*/);//获取客户价格名称
            lst.add(ct);
        }
        cursor.close();
        return lst;
    }

    /**
     * 根据ID查询客户的欠款数及欠款金额
     * @param customerId
     * @return   欠款单数：->debt   欠款金额：->debtMoney
     */
    public static HashMap<String,Object> queryCustomerDebt(DbUtils dbUtils,int customerId)throws Exception{
        HashMap<String,Object> obj = null;
        Cursor cursor = dbUtils.execQuery("SELECT COUNT(kehu_qiankuan.cid),SUM(kehu_qiankuan.wsje) FROM kehu,kehu_shdz,kehu_qiankuan "
                +" WHERE kehu.id = kehu_shdz.kh_id AND kehu_shdz.id = kehu_qiankuan.shdz_id AND kehu.id = kehu_qiankuan.kh_id "
                +"  AND kehu.id = "+customerId+" AND kehu_shdz.id <> 0 AND kehu.id <> 0;");
        if(cursor.moveToNext()){
            obj = new HashMap<String,Object>();
            obj.put("debt",cursor.getInt(0));//获取欠款单张数
            obj.put("debtMoney", FormatUtils.exactNumber(cursor.getFloat(1),"0.00"));//获取欠款总金额
        }
        cursor.close();
        return obj;
    }

    /**
     * 根据关键字查询所有商品
     * @param keyword
     * @return
     * @throws Exception
     */
    public static List<GoodsTable> findGoods(DbUtils dbUtils,String keyword)throws  Exception{
        Selector selector = Selector.from(GoodsTable.class).where(WhereBuilder.b("mc", "like", "%" + keyword + "%"))
                .or("gg", "like", "%" + keyword + "%").or("tm", "like", "%" + keyword + "%")
                .or("kjj","like","%" + keyword + "%");
        System.out.println("----->查询[商品数据]的语句是：" + selector.toString());
        return dbUtils.findAll(selector);
    }

    /**
     * 通过条码查找商品
     * @param context
     * @param code
     * @return
     */
    public static GoodsTable findGoodsByCode(Context context,String code)throws Exception{
        Selector selector = Selector.from(GoodsTable.class).where("tm","=",code);
        return SQLiteUtils.getInstance(context).getDbUtils().findFirst(selector);
    }

    /**
     * 查询商品的件价
     * @param goodsId
     * @param priceName
     * @return
     */
    public static HashMap<String,Object> queryPiecePrice(DbUtils dbUtils,int goodsId,String priceName) throws Exception{
        float piecePrice = 0.0f;//件价
        Selector selector = Selector.from(GoodsModuleTable.class)
                .where("sp_id", "=", goodsId)
                .and("jgmc", "=", priceName);
        GoodsModuleTable goodsModule = dbUtils.findFirst(selector);//查询出来的商品
//        Selector selector1 = Selector.from(CustomerPriceTable.class).where("khdz_id","=",deliverGoodsAddressId)
//                .and("sp_id","=",goodsId).and("hsgx","=",goodsModule.hsgx);
//        CustomerPriceTable customerPriceTable = dbUtils.findFirst(selector1);
        piecePrice = goodsModule.dj;//customerPriceTable == null?goodsModule.dj://customerPriceTable.scfcj;
//        System.out.println("-----> 查询商品的件价的SQL语句是："+selector+"\r\n"+selector1);
        HashMap<String,Object> data = new HashMap<>();
        GoodsTable goods = dbUtils.findFirst(Selector.from(GoodsTable.class).where("id","=",goodsId));
        UnsoldGoodsTable unSoldGoodsTable = dbUtils.findFirst(Selector.from(UnsoldGoodsTable.class).where("sp_id", "=", goods.id));
        if(unSoldGoodsTable!=null) goods.csrq = unSoldGoodsTable.csrq;//如果未售列表存在这个日期，则记录未售列表日期
        else goods.csrq = FormatUtils.standardDatetime(new Date(),"yyyy-MM-dd HH:mm:ss"); //记录当前时间为生产日期
        data.put("piecePrice",piecePrice);
        data.put("goods",goods);
        data.put("goodsModule",goodsModule);
//        data.put("customerPrice",customerPriceTable);
        return data;
    }

    /**
     * 添加购物车商品
     * @param model
     */
    public static void saveShoppingCartGoods(DbUtils dbUtils,FworkGoodsOrderModel model) throws Exception{
        UnsoldGoodsTable unsoldInfo = null;
        if(model.goodsInfo.bzq!=0){//查询保质期数据
            unsoldInfo = dbUtils.findFirst(Selector.from(UnsoldGoodsTable.class)
                    .where("sp_id","=",model.goodsInfo.id).orderBy("dlrq"));
            if(unsoldInfo != null) {//修改下面的参数
                model.goodsInfo.bzq = unsoldInfo.bzts == 0?model.goodsInfo.bzq:unsoldInfo.bzts;//修改保质期天数数据
                model.goodsInfo.csrq = unsoldInfo.csrq;//获取其生成日期
            }
        }
        ShoppingCartGoodsTable table = new ShoppingCartGoodsTable();
        table.xgbs = model.isCanChangePrice;//标识能否修改价格数据
        table.kh_id = model.kh_id;//写入客户网络ID
        table.shdz_id = model.shdz_id;//写入送货地址网络ID
        table.sp_id = model.goodsInfo.id;//写入商品网络ID
        table.tm = model.goodsInfo.tm;//写入商品条码
        table.mc = model.goodsInfo.mc;//写入商品条码
        table.gg = model.goodsInfo.gg;//写入商品规格
        table.xsdw = model.goodsModuleInfo.jldwmc;//写入商品计量单位名称
        table.hsdw = model.goodsInfo.dw;//写入最小单位
        table.hsgx = model.goodsModuleInfo.hsgx;//写入计量单位
        table.xsyj = model.goodsInfo.fcjj;//写入发出价
        table.yfje = model.totalMoney;//写入应付总金额
        table.bzlsj = model.goodsInfo.bzlsj;//写入零售价
        table.xsdwsl = model.pieceNum;//写入件数数量
        table.xsdwss = model.singleNum;//写入数据散数
        table.bzxsdwj = model.finalPrice;//写入标准销售计量单位价格
        table.bz = model.noteInfomation;//写入备注信息
        table.hsdwsl = model.singleNum+model.pieceNum*model.goodsModuleInfo.hsgx;//写入核算单位数量
        table.lx = model.goodsType;//写入类型
        table.zdsj = model.goodsInfo.csrq;//写入产生时间
        table.bz = model.noteInfomation;//写入备注信息
        table.bzq = model.goodsInfo.bzq;//写入保质期
        if(model.shoppingCartGoodsId>0) {//修改购物车商品数据
            table.id = model.shoppingCartGoodsId;//写入订单ID，修改时有该字段值
            dbUtils.update(table, "kh_id", "khdz_id", "sp_id", "tm", "mc", "gg", "xsdw", "hsdw", "hsgx"
                    , "xsyj", "yfje", "bzlsj", "xsdwsl", "xsdwss", "bzxsdwj", "bz", "hsdwsl", "lx", "zdsj", "bz", "bzq");
            return;
        }
        dbUtils.save(table);//保存购物车商品数据
    }


    /**
     * 根据ID删除购物车商品
     * @param id
     */
    public static void deleteShoppingCartGoods(DbUtils dbUtils,int id)throws Exception{
        dbUtils.delete(ShoppingCartGoodsTable.class, WhereBuilder.b("id", "=", id));
    }


    /**
     * 获取某个客户下的所有订单
     * @param addressId 送货地址网络ID
     * @param type -1 全部订单  0 退单 1订单
     */
    public static List<FworkGoodsOrderModel> findAllOrderlst(final DbUtils dbUtils,int kh_id,int addressId,int type) throws Exception{
        List<FworkGoodsOrderModel> lst = new ArrayList<>();
        String sql = "SELECT g.id,p.mc,p.gg,p.dw,g.xsdw,g.hsdw,g.xsdwsl,g.xsdwss,g.kh_id,g.shdz_id,yfje,g.lx,p.tm,g.bz,p.id,g.hsgx,g.bzxsdwj,p.mjsl,g.xgbs,p.kc,p.xl,p.csrq,p.bzq,g.cx_bz " +
                " FROM gouwuche_sp g,kehu k,kehu_shdz s,shangpin p" +
                " WHERE s.id <> 0 AND g.kh_id = k.id AND k.id = s.kh_id AND shdz_id = s.id AND g.sp_id = p.id AND g.shdz_id = "+addressId+" AND k.id = "+ kh_id +" "
                + (type==-1?"":(type==0?"AND g.hsdwsl<0":"AND g.hsdwsl>0"))+" ORDER BY g.id DESC;";
        System.out.println("----->查询某个客户下的所有订单SQL: "+sql);
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            FworkGoodsOrderModel model = new FworkGoodsOrderModel();
            model.goodsInfo = new GoodsTable();
            model.goodsModuleInfo = new GoodsModuleTable();
            model.shoppingCartGoodsId = cursor.getInt(0);//获取购物车本地ID
            model.goodsInfo.mc = cursor.getString(1);//获取商品名称
            model.goodsInfo.gg = cursor.getString(2);//获取商品规格
            //model.goodsInfo.dw = cursor.getString(3);//获取商品最小单位
            model.goodsModuleInfo.jldwmc = cursor.getString(4);//获取计量单位名称
            model.goodsInfo.dw = cursor.getString(5);//获取最小单位
            model.pieceNum = cursor.getInt(6);//获取件数数量
            model.singleNum = cursor.getInt(7);//获取散数数量
            model.kh_id = cursor.getInt(8);//获取客户网络ID
            model.shdz_id = cursor.getInt(9);//获取送货地址网络ID
            model.totalMoney = cursor.getFloat(10);//获取商品应付金额
            model.goodsType = cursor.getInt(11);//获取商品类型
            model.goodsInfo.tm = cursor.getString(12);//获取商品条码
            model.noteInfomation = cursor.getString(13);//获取备注信息
            model.goodsInfo.id = cursor.getInt(14);//获取商品网络ID
            model.goodsModuleInfo.hsgx = cursor.getInt(15);//获取换算关系
            model.finalPrice = cursor.getFloat(16);//获取标准单位销售价
            model.goodsInfo.mjsl = cursor.getInt(17);//获取商品每件数量
            model.isCanChangePrice = cursor.getInt(18);//能否修改价格
            model.goodsInfo.kc = cursor.getFloat(19);//获取商品库存
            model.goodsInfo.xl = cursor.getFloat(20);//获取商品销量
            model.goodsInfo.csrq = cursor.getString(21);//获取商品生产日期
            model.goodsInfo.bzq = cursor.getInt(22);//获取商品保质期
            model.cx_bz = cursor.getString(23);//获取购物车的商品促销标识
            lst.add(model);
        }
        cursor.close();
        return lst;
    }

    /**
     * 查找所有要打印的订单信息
     * @param addressId
     * @param type
     * @return
     */
    public static List<FworkGoodsOrderModel> findAllOrderPrintInfo(DbUtils dbUtils,int kh_id,int addressId,int type)throws Exception{
        List<FworkGoodsOrderModel> orderlist = findAllOrderlst(dbUtils,kh_id,addressId,type);
        if(orderlist!=null && orderlist.size()>0){
            for(FworkGoodsOrderModel item : orderlist) {
                UnsoldGoodsTable unsoldInfo = null;
                if (item.goodsInfo.bzq != 0) {//查询保质期数据
                    unsoldInfo = dbUtils.findFirst(Selector.from(UnsoldGoodsTable.class)
                            .where("sp_id", "=", item.goodsInfo.id).orderBy("dlrq"));
                    if (unsoldInfo != null) {//修改下面的参数
                        try {
                            item.goodsInfo.csrq = FormatUtils.standardDatetime(new SimpleDateFormat("yyyy/MM/dd").parse(unsoldInfo.csrq), "yyyy/MM/dd");//获取其生成日期
                        } catch (Exception e) {
                            item.goodsInfo.csrq = unsoldInfo.csrq.split(" ")[0];
                            e.printStackTrace();
                        }
                        continue;
                    }
                }
                if (TextUtils.isEmpty(item.goodsInfo.csrq)) {//当生产日期为空的时候，默认为当前日期的前10天
                    Calendar calendar = Calendar.getInstance(Locale.CHINA);
                    calendar.set(Calendar.DAY_OF_MONTH, -10);
                    int year = calendar.get(Calendar.YEAR);
                    int month = calendar.get(Calendar.MONTH);
                    int day = calendar.get(Calendar.DAY_OF_MONTH);
                    item.goodsInfo.csrq = (year > 10 ? year + "" : "0" + year) + "/" + (month > 10 ? month + "" : "0" + month) + "/" + (day > 10 ? day + "" : "0" + day);
                }
            }
        }
        return orderlist;
    }

    /**
     * 获取商品订单信息
     * @param addressId
     * @param kh_id
     * @param type 0-退货单 1-订单/销售单
     * @return
     */
    public static HashMap<String,Object> getOrderTotalInfo(DbUtils dbUtils,int addressId,int kh_id,int type)throws Exception {
        HashMap<String,Object> totalInfo = null;
        String orderType = type == 0?" AND hsdwsl < 0 ":" AND hsdwsl > 0 ";
        String sql ="SELECT SUM(yfje),SUM(xsdwsl),SUM(xsdwss),x.cout " +
                " FROM gouwuche_sp,(SELECT SUM(cot) cout FROM (SELECT sp_id,COUNT(sp_id) cot FROM gouwuche_sp WHERE kh_id = "
                + kh_id +" AND shdz_id = " + addressId + orderType + " GROUP BY sp_id)) x " +
                " WHERE kh_id="+kh_id+" AND shdz_id="+addressId+orderType+";";
        Cursor cursor = dbUtils.execQuery(sql);
        while(cursor.moveToNext()){
            if(totalInfo == null) totalInfo = new HashMap<>();
            totalInfo.put("totalMoney",cursor.getFloat(0));//获得总金额
            totalInfo.put("totalPieceNum",cursor.getInt(1));//获取销售单位件数
            totalInfo.put("totalSingleNum",cursor.getInt(2));//获取销售单位散数
            totalInfo.put("totalKinds",cursor.getInt(3));//获取销售总数
        }
        cursor.close();
        return totalInfo;
    }

    /**
     * 查询订单总汇信息
     * @param addressId
     * @return  hash-key: totalKinds - 总种类数  totalMoney-总金额数 totalJs-总件数  totalSingle-总单数
     * @throws Exception
     */
    public static HashMap<String,Object> findAllOrderlstInfo(DbUtils dbUtils,int addressId)throws Exception{
        HashMap<String,Object> objectHashMap = null;
        String sql = "SELECT COUNT(a),SUM(b),SUM(c),SUM(d),SUM(e) FROM (SELECT COUNT(sp_id) a,SUM(yfje) b,SUM(xsdwsl) c,SUM(xsdwss) d,SUM(hsdwsl) e FROM gouwuche_sp WHERE shdz_id = "+addressId+" GROUP BY sp_id);";
        Cursor cursor = dbUtils.execQuery(sql);
        if(cursor.moveToFirst()){
            objectHashMap = new HashMap<>();
            objectHashMap.put("totalKinds",cursor.getInt(0));//获取商品种类数量
            objectHashMap.put("totalMoney",cursor.getFloat(1));//获取订单总金额
            objectHashMap.put("totalJs",cursor.getInt(2));//获取总件数
            objectHashMap.put("totalSingle",cursor.getInt(3));//获取总单数
        }
        cursor.close();
        return objectHashMap;
    }

    /**
     * 删除购物车里面所有数据
     * @param customerId  客户网络ID
     * @param deliverGoodsId  送货地址网络ID
     * @throws Exception
     */
    public static void deleteAllOrderlst(final DbUtils dbUtils,int customerId,int deliverGoodsId) throws Exception{
        dbUtils.delete(ShoppingCartGoodsTable.class,WhereBuilder.b("kh_id","=",customerId).and("shdz_id","=",deliverGoodsId));
    }

    /**
     * 查询所有过时的销售单数据
     * @param date 过期时间
     * @throws Exception
     */
    public static List<SaleTable> findAllOutdateOrderlst(final DbUtils dbUtils,String date) throws Exception{
        List<SaleTable> saleList = null;//销售单集合
        Cursor cursor = dbUtils.execQuery(new SqlInfo("SELECT * FROM xiaoshoudan WHERE djbz = 1 AND date(xdsj) < date('"+date+"');"));
        while(cursor.moveToNext()){
            if(saleList == null) saleList = new ArrayList<>();
            SaleTable saleTable = new SaleTable();
            saleTable.id = cursor.getInt(cursor.getColumnIndex("id"));
            saleTable.ddlx = cursor.getInt(cursor.getColumnIndex("ddlx"));
            saleTable.djbz = cursor.getInt(cursor.getColumnIndex("djbz"));
            saleTable.djh = cursor.getString(cursor.getColumnIndex("djh"));
            saleTable.fhsj = cursor.getString(cursor.getColumnIndex("fhsj"));
            saleTable.js = cursor.getInt(cursor.getColumnIndex("js"));
            saleTable.jsfs = cursor.getInt(cursor.getColumnIndex("jsfs"));
            saleTable.kh_id = cursor.getInt(cursor.getColumnIndex("kh_id"));
            saleTable.pssm = cursor.getString(cursor.getColumnIndex("pssm"));
            saleTable.sfje = cursor.getDouble(cursor.getColumnIndex("sfje"));
            saleTable.shdz_id = cursor.getInt(cursor.getColumnIndex("shdz_id"));
            saleTable.ss = cursor.getInt(cursor.getColumnIndex("ss"));
            saleTable.xdsj = cursor.getString(cursor.getColumnIndex("xdsj"));
            saleTable.zqts = cursor.getInt(cursor.getColumnIndex("zpts"));
            saleList.add(saleTable);
        }
        cursor.close();
        return saleList;
    }


}
